"""SQL预处理服务

该服务负责处理SQL文件的预处理，包括：
1. 提取标记信息（@business_name, @enum等）
2. 清理和格式化SQL
3. 验证SQL有效性
"""

import re
import os
from dataclasses import dataclass, field
from typing import Dict, Optional, Any

@dataclass
class PreprocessResult:
    """预处理结果"""
    cleaned_sql: str
    table: Dict[str, Any] = field(default_factory=dict)
    columns: Dict[str, Dict[str, Any]] = field(default_factory=dict)

class SQLPreprocessService:
    """SQL预处理服务"""

    def __init__(self, format_create_table: bool = False, format_insert: bool = False):
        """初始化SQL预处理服务

        Args:
            format_create_table: 是否格式化CREATE TABLE语句，默认False
            format_insert: 是否格式化INSERT语句，默认False
        """
        self.format_create_table = format_create_table
        self.format_insert = format_insert

    def preprocess(self, sql: str) -> PreprocessResult:
        """预处理SQL，提取标记并清理SQL

        Args:
            sql: 原始SQL语句

        Returns:
            PreprocessResult: 预处理结果，包含清理后的SQL和提取的标记信息
        """
        # 初始化结果
        result = PreprocessResult(cleaned_sql="")

        # 1. 提取标记信息
        # 提取表级标记
        business_name = self._extract_business_name(sql)
        if business_name:
            result.table["business_name"] = business_name

        # 提取配置格式
        config_formats = self._extract_config_formats(sql)
        if config_formats:
            result.table["config_formats"] = config_formats

        # 提取包名
        package = self._extract_package(sql)
        if package:
            result.table["package"] = package

        # 提取字段级标记（枚举值）
        enums = self._extract_enums(sql)
        for field_name, enum_values in enums.items():
            if field_name not in result.columns:
                result.columns[field_name] = {}
            result.columns[field_name]["enum_options"] = enum_values

        # 提取searchable配置
        searchable = self._extract_searchable(sql)
        for field_name, search_config in searchable.items():
            if field_name not in result.columns:
                result.columns[field_name] = {}
            result.columns[field_name]["searchable"] = search_config

        # 2. 清理SQL（移除注释和格式化）
        result.cleaned_sql = self._clean_sql(sql)

        # 3. 验证清理后的SQL是否有效
        if not self._is_valid_sql(result.cleaned_sql):
            raise Exception("无效的SQL语句")

        return result

    def _extract_business_name(self, sql: str) -> Optional[str]:
        """提取业务名称标记"""
        # 1. 先尝试从 @business_name 标记中提取
        pattern = r'--\s*@business_name:\s*(\w+)'
        match = re.search(pattern, sql)
        if match:
            return match.group(1).lower()

        # 2. 如果没有找到标记，从表名中提取
        pattern = r'CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:`)?(\w+)(?:`)?'
        match = re.search(pattern, sql, re.IGNORECASE)
        if match:
            table_name = match.group(1)
            # 移除前缀（如 t_miaoma_）
            parts = table_name.split('_')
            if len(parts) > 2:
                return parts[-1].lower()
            return table_name.lower()

        return None

    def _extract_config_formats(self, sql: str) -> list[str]:
        """提取配置格式标记"""
        pattern = r'--\s*@config:\s*([\w\s,\.]+?)(?:\s*$|\s*\n)'
        match = re.search(pattern, sql)
        if match and match.group(1).strip():
            # 分割并清理每个格式名称
            formats = []
            for fmt in match.group(1).split(','):
                fmt = fmt.strip().replace('.json', '')
                if fmt:
                    formats.append(fmt)
            return formats if formats else ["common"]
        return ["common"]  # 返回默认值

    def _extract_enums(self, sql: str) -> Dict[str, list[Dict[str, str]]]:
        """提取枚举值定义,直接生成[{value:key,label:value}]格式"""
        # 支持等号(=)和连字符(-)两种格式
        pattern = r'--\s*@enum:\s*(\w+)\s+((?:\d+[-=]\S+(?:\s+|$))+)'
        matches = re.finditer(pattern, sql)
        enums = {}

        for match in matches:
            field_name = match.group(1).lower()
            values_str = match.group(2)

            # 直接生成[{value:key,label:value}]格式
            values = []
            # 同时支持等号和连字符格式
            for value_match in re.finditer(r'(\d+)([-=])(\S+)(?:\s+|$)', values_str):
                key = value_match.group(1)
                value = value_match.group(3)
                values.append({
                    "value": key,
                    "label": value
                })

            enums[field_name] = values

        return enums

    def _extract_searchable(self, sql: str) -> Dict[str, Dict[str, str]]:
        """提取字段的searchable配置"""
        pattern = r'--\s*@searchable:\s*(\w+)\s+(like|equal)\s*'
        matches = re.finditer(pattern, sql)
        searchable = {}

        for match in matches:
            field_name = match.group(1).lower()
            search_type = match.group(2).lower()
            searchable[field_name] = {"type": search_type}

        return searchable

    def _clean_sql(self, sql: str) -> str:
        """清理SQL语句，保持良好的缩进和可读性"""
        # 1. 移除多行注释（不在字符串内的）
        cleaned_sql = ""
        in_string = False
        in_multiline_comment = False
        i = 0
        while i < len(sql):
            if sql[i] == "'":
                if not in_multiline_comment:
                    in_string = not in_string
                cleaned_sql += sql[i]
                i += 1
            elif not in_string and sql[i:i+2] == "/*":
                in_multiline_comment = True
                i += 2
            elif not in_string and sql[i:i+2] == "*/":
                in_multiline_comment = False
                i += 2
            elif not in_multiline_comment:
                cleaned_sql += sql[i]
                i += 1
            else:
                i += 1

        # 2. 移除注释行，保留缩进
        lines = []
        for line in cleaned_sql.split('\n'):
            # 移除行内注释（在非字符串内的--后面的内容）
            in_string = False
            comment_start = -1
            for i, char in enumerate(line):
                if char == "'":
                    in_string = not in_string
                elif not in_string and char == '-' and i + 1 < len(line) and line[i + 1] == '-':
                    comment_start = i
                    break

            if comment_start >= 0:
                line = line[:comment_start]

            line = line.rstrip()  # 只移除行尾空白字符
            if not line:
                continue
            lines.append(line)

        # 3. 合并为单个字符串
        sql = '\n'.join(lines)

        # 4. 移除反引号
        sql = re.sub(r'`([^`]+)`', r'\1', sql)

        # 5. 标准化数据类型
        type_patterns = [
            (r'\bint\b', 'INT'),
            (r'\bbigint\b', 'BIGINT'),
            (r'\bvarchar\b', 'VARCHAR'),
            (r'\btext\b', 'TEXT'),
            (r'\btimestamp\b', 'TIMESTAMP'),
            (r'\bdate\b', 'DATE'),
            (r'\btinyint\b', 'TINYINT')
        ]
        for pattern, replacement in type_patterns:
            sql = re.sub(pattern, replacement, sql, flags=re.IGNORECASE)

        # 6. 标准化关键字
        keyword_patterns = [
            (r'\bcreate\s+table\b', 'CREATE TABLE'),
            (r'\bprimary\s+key\b', 'PRIMARY KEY'),
            (r'\bnot\s+null\b', 'NOT NULL'),
            (r'\bauto_increment\b', 'AUTO_INCREMENT'),
            (r'\bdefault\b', 'DEFAULT'),
            (r'\bcomment\b', 'COMMENT'),
            (r'\bengine\s*=\s*', 'ENGINE='),
            (r'\bcharset\s*=\s*', 'CHARSET=')
        ]
        for pattern, replacement in keyword_patterns:
            sql = re.sub(pattern, replacement, sql, flags=re.IGNORECASE)

        return sql

    def _is_valid_sql(self, sql: str) -> bool:
        """验证SQL是否有效"""
        try:
            # 检查是否包含CREATE TABLE语句
            if not re.search(r'CREATE\s+TABLE\s+\w+\s*\(', sql, re.IGNORECASE):
                raise Exception("未找到CREATE TABLE语句")

            # 检查括号是否匹配
            if sql.count('(') != sql.count(')'):
                raise Exception("括号不匹配")

            # 检查是否包含必要的字段定义
            if not re.search(r'\(\s*\w+\s+\w+[^)]+\)', sql, re.IGNORECASE):
                raise Exception("未找到有效的字段定义")

            # 检查是否包含必要的表属性
            if not re.search(r'ENGINE\s*=\s*\w+', sql, re.IGNORECASE):
                raise Exception("未找到表引擎定义")

            return True
        except Exception as e:
            raise Exception(f"SQL语法无效: {str(e)}")

    def _extract_package(self, sql: str) -> str:
        """提取包名标记"""
        pattern = r'--\s*@package:\s*([\w\.]+)'
        match = re.search(pattern, sql)
        if match:
            return match.group(1)
        return "com.miaoma.auto"

    def process_fullpath(self, file_path: str) -> PreprocessResult:
        """处理完整路径的SQL文件

        Args:
            file_path: SQL文件的完整路径

        Returns:
            PreprocessResult: 预处理结果

        Raises:
            FileNotFoundError: 当文件不存在时
            Exception: 当SQL内容无效时
        """
        if not os.path.exists(file_path):
            raise FileNotFoundError(f"SQL文件不存在: {file_path}")

        with open(file_path, 'r', encoding='utf-8') as f:
            sql_content = f.read()

        return self.preprocess(sql_content)